package com.cn.hotel.dao.Impl;

import com.cn.hotel.dao.IOrderDao;
import com.cn.hotel.model.OrderModel;
import com.cn.hotel.model.UserModel;
import com.cn.hotel.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrderImpl implements IOrderDao {
    /**
     * 添加新的订单信息
     * @return
     */
    @Override
    public int add(OrderModel orderModel) {
        String sql ="insert into table_order(username,sex,roomstyle,channel,phone,reservation_time,in_time,out_time) values('"+orderModel.getUsername()  +"'," +
                "'"+orderModel.getSex()+"','"+orderModel.getRoomType()+"','"+orderModel.getChannel()+"','"+orderModel.getPhone()+"'," +
                "'"+orderModel.getReservationTime()+"','"+orderModel.getCheckInTime()+"','"+orderModel.getCheckOutTime()+"')";
        return JDBC.executeUpdate(sql);
    }

    /**
     * 订单模糊查询 sql
     * @param orderModel
     * @return
     */
    @Override
    public List<OrderModel> findByKeyWord(OrderModel orderModel) {
        String sql = "select * from table_order where 1=1";
        if (!"".equals(orderModel.getUsername())) {
            sql += " and username like '%" + orderModel.getUsername() + "%'";
        }
        if (!"".equals(orderModel.getSex())&&!"全部".equals(orderModel.getSex())) {
            sql += " and sex = '" + orderModel.getSex() + "'";
        }
        if (!"".equals(orderModel.getRoomType())&&!"全部".equals(orderModel.getRoomType())) {
            sql += " and roomstyle = '" + orderModel.getRoomType() + "'";
        }
        if (!"".equals(orderModel.getChannel())&&!"全部".equals(orderModel.getChannel())) {
            sql += " and channel= '" + orderModel.getChannel() + "'";
        }
        if (!"".equals(orderModel.getPhone())) {
            sql += " and phone like '" + orderModel.getPhone() + "'";
        }
        if (!"".equals(orderModel.getReservationTime())) {
            sql += " and reservation_time = '" + orderModel.getReservationTime() + "'";
        }
        if (!"".equals(orderModel.getCheckInTime())) {
            sql += " and in_time = '" + orderModel.getCheckInTime() + "'";
        }
        if (!"".equals(orderModel.getCheckOutTime())) {
            sql += " and out_time = '" + orderModel.getCheckOutTime() + "'";
        }
        sql += " order by id asc" + " limit  "+(orderModel.getPageModel().getPageNum()-1)*orderModel.getPageModel().getPageSize()+"" +
                ","+orderModel.getPageModel().getPageSize()+"";

        List<OrderModel> list = new ArrayList<>();
        ResultSet resultSet = JDBC.getResultSet(sql);
        try {
            while (resultSet.next()) {
                OrderModel orderModel1 = new OrderModel();
                orderModel1.setId(resultSet.getInt("id"));
                orderModel1.setUsername(resultSet.getString("username"));
                orderModel1.setSex(resultSet.getString("sex"));
                orderModel1.setRoomType(resultSet.getString("roomstyle"));
                orderModel1.setChannel(resultSet.getString("channel"));
                orderModel1.setPhone(resultSet.getString("phone"));
                orderModel1.setReservationTime(resultSet.getString("reservation_time"));
                orderModel1.setCheckInTime(resultSet.getString("in_time"));
                orderModel1.setCheckOutTime(resultSet.getString("out_time"));
                list.add(orderModel1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBC.close();
        }
        return list;
    }

    @Override
    public int getCount(OrderModel orderModel) {
        String sql = "select count(*) count from table_order where 1=1";
        if (!"".equals(orderModel.getUsername())) {
            sql += " and username like '%" + orderModel.getUsername() + "%'";
        }
        if (!"".equals(orderModel.getSex())&&!"全部".equals(orderModel.getSex())) {
            sql += " and sex = '" + orderModel.getSex() + "'";
        }
        if (!"".equals(orderModel.getRoomType())&&!"全部".equals(orderModel.getRoomType())) {
            sql += " and roomstyle = '" + orderModel.getRoomType() + "'";
        }
        if (!"".equals(orderModel.getChannel())&&!"全部".equals(orderModel.getChannel())) {
            sql += " and channel= '" + orderModel.getChannel() + "'";
        }
        if (!"".equals(orderModel.getPhone())) {
            sql += " and phone like '" + orderModel.getPhone() + "'";
        }
        if (!"".equals(orderModel.getReservationTime())) {
            sql += " and reservation_time = '" + orderModel.getReservationTime() + "'";
        }
        if (!"".equals(orderModel.getCheckInTime())) {
            sql += " and in_time = '" + orderModel.getCheckInTime() + "'";
        }
        if (!"".equals(orderModel.getCheckOutTime())) {
            sql += " and out_time = '" + orderModel.getCheckOutTime() + "'";
        }
        ResultSet resultSet = JDBC.getResultSet(sql);
        int count = 0;
        try {
            resultSet.next();
            count = resultSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 通过id查询
     * @param temp
     * @return
     */
    @Override
    public OrderModel findById(Integer temp) {
        StringBuilder sql = new StringBuilder();
        if (temp!=0){
            sql.append("select * from table_order where id="+temp);
        }
        ResultSet resultSet = JDBC.getResultSet(sql.toString());
        OrderModel model = new OrderModel();
        try{
            while (resultSet.next()){
                model.setId(resultSet.getInt("id"));
                model.setUsername(resultSet.getString("username"));
                model.setSex(resultSet.getString("sex"));
                model.setRoomType(resultSet.getString("roomstyle"));
                model.setChannel(resultSet.getString("channel"));
                model.setPhone(resultSet.getString("phone"));
                model.setReservationTime(resultSet.getString("reservation_time"));
                model.setCheckInTime(resultSet.getString("in_time"));
                model.setCheckOutTime(resultSet.getString("out_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return model;
    }

    /**
     * 保存编辑
     * @param orderModel
     * @return
     */
    @Override
    public int orderEdit(OrderModel orderModel) {
        StringBuilder sql =new StringBuilder();
        sql.append(" update table_order set");
        if (orderModel.getUsername()!=null&&!"".equals(orderModel.getUsername())){
            sql.append(" username='"+orderModel.getUsername()+"',");
        }
        if (orderModel.getSex()!=null&&!"".equals(orderModel.getSex())){
            sql.append(" sex='"+orderModel.getSex()+"',");
        }
        if (orderModel.getPhone()!=null&&!"".equals(orderModel.getPhone())){
            sql.append(" phone='"+orderModel.getPhone()+"',");
        }
        if (orderModel.getRoomType()!=null&&!"".equals(orderModel.getRoomType())){
            sql.append(" roomstyle='"+orderModel.getRoomType()+"',");
        }
        if (orderModel.getChannel()!=null&&!"".equals(orderModel.getChannel())){
            sql.append(" channel='"+orderModel.getChannel()+"',");
        }
        if (orderModel.getReservationTime()!=null&&!"".equals(orderModel.getReservationTime())){
            sql.append(" reservation_time='"+orderModel.getReservationTime()+"',");
        }
        if (orderModel.getCheckInTime()!=null&&!"".equals(orderModel.getCheckInTime())){
            sql.append(" in_time='"+orderModel.getCheckInTime()+"',");
        }
        if (orderModel.getCheckOutTime()!=null&&!"".equals(orderModel.getCheckOutTime())){
            sql.append(" out_time='"+orderModel.getCheckOutTime()+"',");
        }
        sql.append(" remark = '' where id="+orderModel.getId()+"");
        return JDBC.executeUpdate(sql.toString());
    }
    //删除
    @Override
    public int delete(Integer temp) {
        StringBuilder sql = new StringBuilder();
        if (temp!=0){
            sql.append(" delete from table_order where id ="+temp);
        }
        return JDBC.executeUpdate(sql.toString());
    }

}
